package com.aquila.datacontrol.database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

import com.aquila.datacontrol.object.NewBibleObject;
/**
 * 读取数据的基础类
 * @author Administrator
 *
 */
public class NewBaseDataSqlite extends SQLiteOpenHelper {
	public static final String TABLE_NAME = "holybible";
	public static final int Version =1;
	private static final String DATABASE_NAME = "lzzVersionBible.db";
	private SQLiteDatabase db;
	
	private final String KEY_ID = "id" ;
	
	private final String KEY_BOOK_STRING = "bookName" ; //书卷全名
	private final String KEY_BOOK_SHORT_STRING = "shortBookName" ; //书卷的缩写
//	private final String KEY_EN_BOOK_STRING = "enBookName" ; //书卷的缩写
//	private final String KEY_EN_BOOK_SHORT_STRING = "enShortBookName" ; //书卷的缩写
	private final String KEY_BOOK_INDEX = "bookIndex" ;	//书卷的索引
	private final String KEY_CHAPTER_INDEX = "chapterIndex" ; // 章节的索引
	private final String KEY_CHAPTER_COUNT = "chapterCount" ; // 章节总数
	
	private final String KEY_CN_CONTENT = "sectionText" ; //章节正文
//	private final String KEY_EN_CONTENT= "enSectionText" ; // 英文内容

	private final String KEY_SECTION_ID = "sectionId" ; //带标题的经节索引
	private final String KEY_SECTION_INDEX = "sectionIndex" ; //经节索引
	private final String KEY_SECTION_COUNT = "sectionCount" ; // 经节数目
	private final String KEY_IS_TITLE= "isTitle" ; // 是否是标题
	private final String KEY_IS_NEWTEASTMENT = "isNew" ; //是否是新约

	private final String KEY_FLAG_01 = "firstFlag" ; // 备用属性1
	private final String KEY_FLAG_02 = "secontFlag" ; // 备用属性2
	
	private final String KEY_belongTo = "belongTo" ;
	
	
	
	/***
	 * 创建数据库表
	 * @param db
	 *2012-7-30 下午1:15:53
	 */
	private void createTable(SQLiteDatabase db){
		String sqlstr = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "+
				KEY_ID +" INTEGER PRIMARY KEY, "+
				KEY_IS_NEWTEASTMENT +" INTEGER, "+
	            KEY_BOOK_STRING + "  VARCHAR2(30), "+
	            KEY_BOOK_SHORT_STRING+ "  VARCHAR2(30), "+
//	            KEY_EN_BOOK_STRING+ "  VARCHAR2(50), "+
//	            KEY_EN_BOOK_SHORT_STRING+ "  VARCHAR2(30), "+
	            KEY_belongTo + "  VARCHAR2(30), "+
	            KEY_BOOK_INDEX+ "  INTEGER, "+
	            KEY_CHAPTER_INDEX+ "  INTEGER, "+
	            KEY_SECTION_INDEX + " INTEGER, " +
	            KEY_CN_CONTENT +" TEXT, "+
	            KEY_SECTION_ID + " INTEGER, " +
	            KEY_IS_TITLE+ " INTEGER, " +
	            KEY_CHAPTER_COUNT+ "  INTEGER, "+
	            KEY_SECTION_COUNT + " INTEGER, "+
//	            KEY_EN_CONTENT+ " TEXT, " +
	            KEY_FLAG_01 + " TEXT, " +
	            KEY_FLAG_02+ " TEXT);";
		try{
			db.execSQL(sqlstr);
		}catch (SQLException e) {
			e.printStackTrace() ;
		}
	}
	private Context context ;
	public NewBaseDataSqlite(Context context) {
		super(context, DATABASE_NAME, null, Version);
		this.context = context ;
		db = getWritableDatabase() ;
		createTable(db) ;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}
	
	/***
	 * 打开数据库
	 *2012-7-30 下午1:37:13
	 */
	public void open() {
		if (db == null || !db.isOpen()) {
			db = getWritableDatabase() ;
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close(){
		if (db.isOpen()) {
			db.close() ;
		}
	}
	
//	/**
//	 * 获取章节文本
//	 * @param bookIndex
//	 * @param chapterIndex
//	 * @return
//	 * String
//	 * 2012-11-7 上午9:50:19
//	 */
//	public String getChapterString(int bookIndex, int chapterIndex){
//		String sqlString = "select * from "+TABLE_NAME+" where "+
//				KEY_BOOK_INDEX +" = '" + bookIndex +"' and " +
//				KEY_CHAPTER_INDEX+ " = '" + chapterIndex + "'";
//		Cursor cursor = db.rawQuery(sqlString, null) ;
//		if (cursor!=null) {
//		cursor.moveToFirst() ;
//		String string =cursor.getString(cursor.getColumnIndex(KEY_CHAPTER_STRING)) ; 
//        cursor.close() ;
//		return string ;
//        }
//		return "" ;
//	}
	
	/**
	 * 获取基本的信息
	 * @param bookIndex
	 * @return
	 * BaseObject
	 * 2012-11-7 上午9:56:39
	 */
	public NewBibleObject getBookInfo(int bookIndex){
		NewBibleObject object = null ;
		String sqlString = "select * from "+TABLE_NAME+" where "+
				KEY_BOOK_INDEX +" = '" + bookIndex  +"'";
		Cursor cursor = db.rawQuery(sqlString, null) ;
		if (cursor!=null) {
			cursor.moveToFirst() ;
			object = new NewBibleObject() ;
			object.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID))) ;
			object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_BOOK_INDEX))) ;
			object.setShortBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOK_SHORT_STRING))) ;
			object.setBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOK_STRING))) ;
			object.setChapterCount(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_COUNT))) ;
			object.setChapterIndex(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) ;
//			object.setContentString(cursor.getString(cursor.getColumnIndex(KEY_CONTENT_STRING))) ;
//			object.setKeyWords(cursor.getString(cursor.getColumnIndex(KEY_KEY_WORDS))) ;
//			object.setSectionCount(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_COUNT))) ;
//			object.setSectionIndex(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_INDEX))) ;
//			object.setTimeString(cursor.getString(cursor.getColumnIndex(KEY_ORDER_TIME))) ;
//			object.setChapterString(cursor.getString(cursor.getColumnIndex(KEY_CHAPTER_STRING))) ;
			
			object.setIsNew(cursor.getInt(cursor.getColumnIndex(KEY_IS_NEWTEASTMENT))) ;
			object.setFirstFlag(cursor.getString(cursor.getColumnIndex(KEY_FLAG_01))) ;
			object.setSecontFlag(cursor.getString(cursor.getColumnIndex(KEY_FLAG_02))) ;
			cursor.close() ;
			
			
		}
		return object ;
		
	}
	
	/**
	 * 加载数据库所有记录
	 * @return
	 *2012-8-8 上午10:12:42
	 */
	public ArrayList<NewBibleObject> getAllData(){
		ArrayList<NewBibleObject> arrayList = new ArrayList<NewBibleObject>() ;
		String sql = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sql, null) ;
		if (cursor.getCount() == 0 ) {
        	return null;
        }
		cursor.moveToFirst() ;
		do {
			arrayList.add(readDataResultObject(cursor)) ;
        } while(cursor.moveToNext()) ;

		cursor.close() ;
		return arrayList ;
	}
	
	
	/**
	 * 插入一条数据
	 * @param object
	 *2012-8-8 上午10:09:36
	 */
	public boolean insertData(NewBibleObject object){
		if (object == null) {
			return false;
		}
			ContentValues contentValues = inputContentValues(object) ;
			db.insert(TABLE_NAME, null, contentValues) ;
			return true;
	}
	
	
	
	
//	private boolean judegeExistData(SearchResultObject object){
//		int len = getDataCount() ;
//		ArrayList<SearchResultObject> arrayList = getAllData() ;
//		SearchResultObject tempObject ;
//		for (int i = 0; i < len; i++) {
//		tempObject = arrayList.get(i) ;
//        	if ((object.getContentString().equals(tempObject.getContentString())
//        			&& object.getBookIndex() == tempObject.getBookIndex())
//        			) {
//            	return true ;
//            }
//        }
//		return false ;
//	}
	
	/**
	 * 判断数据是否重复
	 * @param object
	 * @return
	 * boolean
	 * 2012-10-18 下午5:29:19
	 */
	private boolean hasExistData(NewBibleObject object){
		String sqlString = "select * from "+TABLE_NAME+" where "+
				KEY_CN_CONTENT +" ='" + object.getSectionText()+"' and " +
				KEY_BOOK_INDEX + " = '" + object.getBookIndex() + "'";
		Cursor cursor = db.rawQuery(sqlString, null) ;
		if (cursor != null && cursor.getCount() > 0) {
			return true ;
		}
		return false ;
	}
	
	/**
	 * 删除数据
	 * @param object
	 *2012-8-8 上午11:46:28
	 */
	public void deleteData(NewBibleObject object){
		if (object== null) {
			return ;
		}
		if (object.getId() > 0) {
			db.delete(TABLE_NAME, KEY_ID + " = " +object.getId(), null) ;
		}
		else {
			Toast.makeText(context, "查无此数据", Toast.LENGTH_SHORT).show() ;	
		}
	}
	
	
	public void deleteAllData(){
		db.delete(TABLE_NAME, null, null) ;
	}
	/**
	 * 更新一条数据
	 * @param object
	 *2012-8-8 上午11:44:15
	 */
	public void updateData(NewBibleObject object){
		if (object == null) {
			return ;
		}
		ContentValues contentValues = inputContentValues(object) ;
		db.update(TABLE_NAME, contentValues, KEY_ID +" = "+object.getId(), null) ;
	}
	
	
	/**
	 * 获取保存历史的记录
	 * @return
	 *2012-7-30 下午1:57:52
	 */
	public int getDataCount(){
		String sqlString = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sqlString, null) ;
		return cursor.getCount() ;
	}
	
	
	
	/**
	 * 封装读取数据的方法
	 * @param cursor
	 * @return
	 *2012-8-8 上午10:00:08
	 */
	private NewBibleObject readDataResultObject(Cursor cursor){
		NewBibleObject object= new NewBibleObject() ;
		object.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID))) ;
		object.setBookIndex(cursor.getInt(cursor.getColumnIndex(KEY_BOOK_INDEX))) ;
		object.setShortBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOK_SHORT_STRING))) ;
		object.setBookName(cursor.getString(cursor.getColumnIndex(KEY_BOOK_STRING))) ;
		object.setChapterCount(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_COUNT))) ;
		object.setChapterIndex(cursor.getInt(cursor.getColumnIndex(KEY_CHAPTER_INDEX))) ;
		object.setSectionText(cursor.getString(cursor.getColumnIndex(KEY_CN_CONTENT))) ;
		object.setBelongTo(cursor.getString(cursor.getColumnIndex(KEY_belongTo))) ;
		object.setSectionCount(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_COUNT))) ;
		object.setSectionIndex(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_INDEX))) ;
		object.setSectionId(cursor.getInt(cursor.getColumnIndex(KEY_SECTION_ID))) ;
//		object.setEnSectionText(cursor.getString(cursor.getColumnIndex(KEY_EN_CONTENT))) ;
		object.setIsTitle(cursor.getInt(cursor.getColumnIndex(KEY_IS_TITLE))) ;
		object.setIsNew(cursor.getInt(cursor.getColumnIndex(KEY_IS_NEWTEASTMENT))) ;
		object.setFirstFlag(cursor.getString(cursor.getColumnIndex(KEY_FLAG_01))) ;
		object.setSecontFlag(cursor.getString(cursor.getColumnIndex(KEY_FLAG_02))) ;
		
		return object ;
	}
	
	
	/**
	 * 存数据封装的方法
	 * @param object
	 * @return
	 *2012-8-8 上午9:45:14
	 */
	private ContentValues inputContentValues(NewBibleObject object){
		ContentValues cv = new ContentValues() ;
		cv.put(KEY_belongTo, object.getBelongTo()) ;
		cv.put(KEY_BOOK_INDEX, object.getBookIndex()) ;
		cv.put(KEY_BOOK_SHORT_STRING, object.getShortBookName()) ;
		cv.put(KEY_BOOK_STRING, object.getBookName()) ;
//		cv.put(KEY_EN_BOOK_STRING, object.getEnBookName()) ;
//		cv.put(KEY_EN_BOOK_SHORT_STRING, object.getEnShortBookName()) ;
		
		cv.put(KEY_CHAPTER_COUNT, object.getChapterCount()) ;
		cv.put(KEY_CHAPTER_INDEX, object.getChapterIndex()) ;
		cv.put(KEY_CN_CONTENT, object.getSectionText()) ;
		cv.put(KEY_SECTION_COUNT, object.getSectionCount()) ;
		cv.put(KEY_SECTION_INDEX, object.getSectionIndex()) ;
		cv.put(KEY_SECTION_ID, object.getSectionId()) ;
//		cv.put(KEY_EN_CONTENT, object.getEnSectionText()) ;
		cv.put(KEY_IS_TITLE, object.getIsTitle()) ;
		cv.put(KEY_IS_NEWTEASTMENT, object.getIsNew()) ;
		cv.put(KEY_FLAG_01, object.getFirstFlag()) ;
		cv.put(KEY_FLAG_02, object.getSecontFlag());
		return cv ;
	}
	
	
}
